--EXEC SP_GET_WAFERS_FOR_MRB_RELEASE

alter PROC SP_GET_WAFERS_FOR_MRB_RELEASE
AS 
BEGIN
	DECLARE @LOT_AND_WAFER_IDS_CURSOR CURSOR
	DECLARE @LOT_ID INT
	DECLARE @WAFER_ID INT
	DECLARE @BIN_INFO VARCHAR(1000)
	DECLARE @IB_BIN_QUANTITIES VARCHAR(1000)
	DECLARE @LOOP_VAR INT
	DECLARE @GOOD_BINS_COUNT INT
	DECLARE @SQL_QUERY NVARCHAR(1000)
	DECLARE @PROCESSSTEP_ID INT

	CREATE TABLE #RESULTS_TABLE ( LOT_ID INT,LOT_NUMBER VARCHAR(50),PART_NUMBER VARCHAR(50),WAFER_ID INT,GOOD_DICE INT,BIN_1 INT,BIN_2 INT,BIN_3 INT,BIN_4 INT,
	BIN_5 INT,BIN_6 INT,BIN_7 INT,BIN_8 INT,BIN_9 INT,BIN_10 INT,BIN_11 INT,BIN_12 INT,BIN_13 INT,BIN_14 INT,BIN_15 INT, 
	L_VENDOR_NAME VARCHAR(100), DATE_OUT VARCHAR(10))
	
	INSERT INTO #RESULTS_TABLE SELECT LT.LOT_ID,LT.LOT_NUMBER,P.PART_NUMBER,IB.WAFER_ID,IB.GOOD_DICE,IB.BIN_1,IB.BIN_2,IB.BIN_3,IB.BIN_4,
	IB.BIN_5,IB.BIN_6,IB.BIN_7,IB.BIN_8,IB.BIN_9,IB.BIN_10,IB.BIN_11,IB.BIN_12,IB.BIN_13,IB.BIN_14,IB.BIN_15, 
	L.L_VENDOR_NAME+'/'+AD.CITY AS L_VENDOR_NAME, CONVERT(VARCHAR, SLH.SL_DATE_OUT, 101) AS DATE_OUT FROM  
	LOT_TABLE LT,PRODUCT P, INFORMATIVE_BINNING IB, LOCATION L, SORT_LOT_HISTORY SLH, ADDRESS AD WHERE 
	P.PROCESSSTEP_ID = LT.PROCESSSTEP_ID AND LT.LOT_ID = IB.LOT_ID AND IB.MRB_LIMIT = 0 AND 
	LT.LOCATION_ID = L.LOCATION_ID AND LT.LOT_ID = SLH.LOT_ID AND L.ADDRESS_ID = AD.ADDRESS_ID
	AND ( SLH.TIMESTAMP = (SELECT MAX(TIMESTAMP) FROM SORT_LOT_HISTORY WHERE LOT_ID = LT.LOT_ID ) )
	AND ((SELECT COUNT(*) FROM ASSM_RELEASE_LOT_TABLE WHERE LOT_ID = LT.LOT_ID AND WAFER_ID = IB.WAFER_ID) = 0) 
	ORDER BY SL_DATE_OUT DESC
	
	
	SET @LOT_AND_WAFER_IDS_CURSOR = CURSOR FOR SELECT DISTINCT ARLT.LOT_ID, ARLT.WAFER_ID, LT.PROCESSSTEP_ID FROM ASSM_RELEASE_TABLE ART, 
					INFORMATIVE_BINNING IB, ASSM_RELEASE_LOT_TABLE ARLT, LOT_TABLE LT WHERE ART.ASSEMBLY_RELEASE_ID = ARLT.ASSEMBLY_RELEASE_ID 
					AND  IB.MRB_LIMIT = 0 AND IB.RMA_LIMIT = 1 AND ARLT.LOT_ID = IB.LOT_ID AND LT.LOT_ID = ARLT.LOT_ID AND
					ARLT.WAFER_ID = IB.WAFER_ID AND ART.STATUS NOT IN ('CANCELLED')
	open @LOT_AND_WAFER_IDS_CURSOR 
	FETCH FROM @LOT_AND_WAFER_IDS_CURSOR INTO @LOT_ID, @WAFER_ID, @PROCESSSTEP_ID
	WHILE( @@FETCH_STATUS = 0 )
	BEGIN
	    	SET @BIN_INFO = DBO.fn_get_bins_used_for_assembly_release(@LOT_ID, @WAFER_ID)
		SET @IB_BIN_QUANTITIES = ''
		SET @LOOP_VAR = 1
		select @GOOD_BINS_COUNT = count(*) from wip_Step_bin_table where WSBT_Bin_Type = 'ACCEPT' and processStep_id = @PROCESSSTEP_ID
		WHILE( @LOOP_VAR <= @GOOD_BINS_COUNT )
		BEGIN
			IF( CHARINDEX( '&'+CAST(@LOOP_VAR AS VARCHAR)+'&', @BIN_INFO ) = 0 )
			BEGIN
				SET @IB_BIN_QUANTITIES = @IB_BIN_QUANTITIES + 'BIN_'+CAST(@LOOP_VAR AS VARCHAR)+'+'
				PRINT @IB_BIN_QUANTITIES
			END				
			SET @LOOP_VAR = @LOOP_VAR + 1
		END
		SET @IB_BIN_QUANTITIES = SUBSTRING(@IB_BIN_QUANTITIES, 0, LEN(@IB_BIN_QUANTITIES))
		IF( LEN(@IB_BIN_QUANTITIES ) > 0 )
		BEGIN
			SET @SQL_QUERY = 'INSERT INTO #RESULTS_TABLE SELECT LT.LOT_ID,LT.LOT_NUMBER,P.PART_NUMBER,IB.WAFER_ID,'+@IB_BIN_QUANTITIES+' AS GOOD_DICE,IB.BIN_1,IB.BIN_2,IB.BIN_3,IB.BIN_4,
					IB.BIN_5,IB.BIN_6,IB.BIN_7,IB.BIN_8,IB.BIN_9,IB.BIN_10,IB.BIN_11,IB.BIN_12,IB.BIN_13,IB.BIN_14,IB.BIN_15, L.L_VENDOR_NAME+''/''+AD.CITY AS L_VENDOR_NAME, 
					CONVERT(VARCHAR, SLH.SL_DATE_OUT, 101) AS DATE_OUT FROM LOT_TABLE LT,PRODUCT P, 
					INFORMATIVE_BINNING IB, LOCATION L, SORT_LOT_HISTORY SLH, ADDRESS AD WHERE P.PROCESSSTEP_ID = LT.PROCESSSTEP_ID 
					AND LT.LOT_ID = IB.LOT_ID AND LT.LOCATION_ID = L.LOCATION_ID AND LT.LOT_ID = SLH.LOT_ID AND 
					L.ADDRESS_ID = AD.ADDRESS_ID AND IB.LOT_ID = '+CAST(@LOT_ID AS NVARCHAR)+' AND IB.WAFER_ID = '+CAST(@WAFER_ID AS NVARCHAR)+' ORDER BY SL_DATE_OUT DESC'
			EXEC SP_EXECUTESQL @SQL_QUERY
		END
		FETCH FROM @LOT_AND_WAFER_IDS_CURSOR INTO @LOT_ID, @WAFER_ID, @PROCESSSTEP_ID
	END
	SELECT * FROM #RESULTS_TABLE ORDER BY DATE_OUT DESC
END





				 
